package com.njcb.ams.repository.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.njcb.ams.pojo.dto.TableSpaceTableUseDTO;
import com.njcb.ams.pojo.dto.TableSpaceUseDTO;

@Repository
public class TableSpaceMonitoringDAO{

	public TableSpaceMonitoringDAO() {
	}

	@Autowired
	private JdbcTemplate jdbcTemplate;

	/**
	 * 表空间数据段占用空间情况
	 * @return
	 */
	public List<TableSpaceTableUseDTO> getTableSpaceTableUse() {
		StringBuilder sbsql = new StringBuilder(200);
		sbsql.append(" select segment_name, bytes / 1024 / 1024 as table_size, tablespace_name ");
		sbsql.append("   from dba_segments ");
		sbsql.append("  where tablespace_name not in ('SYSTEM', 'SYSAUX', 'EXAMPLE') and segment_type = 'TABLE' ");
		sbsql.append("  order by bytes desc ");
		sbsql.append("  ");
		List<Map<String, Object>> retList = jdbcTemplate.queryForList(sbsql.toString());
		List<TableSpaceTableUseDTO> dtoList = new ArrayList<TableSpaceTableUseDTO>();
		for (Map<String, Object> map : retList) {
			TableSpaceTableUseDTO tableUse = new TableSpaceTableUseDTO();
			tableUse.setSegmentName((String)map.get("segment_name"));
			tableUse.setTableSize(String.valueOf(map.get("table_size")));
			tableUse.setTablespaceName((String)map.get("tablespace_name"));
			dtoList.add(tableUse);
		}
		return dtoList;
	}
	
	
	/**
	 * 查看表空间使用情况
	 * @return
	 */
	public List<TableSpaceUseDTO> getTableSpaceUse() {
		StringBuilder sbsql = new StringBuilder(200);
		sbsql.append(" SELECT UPPER(F.TABLESPACE_NAME) as TABLESPACE_NAME, ");
		sbsql.append("        　　D.TOT_GROOTTE_MB as TOT_GROOTTE_MB, ");
		sbsql.append("        　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES as USE_GROOTTE_MB, ");
		sbsql.append("        　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / ");
		sbsql.append("                        D.TOT_GROOTTE_MB * 100, 2),'990.99') || '%' as USE_PERCENTAGE, ");
		sbsql.append("        　　F.TOTAL_BYTES as TOTAL_BYTES, ");
		sbsql.append("        　　F.MAX_BYTES as MAX_BYTES ");
		sbsql.append("   FROM (SELECT TABLESPACE_NAME, ");
		sbsql.append("                　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ");
		sbsql.append("                　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES ");
		sbsql.append("           FROM SYS.DBA_FREE_SPACE 　　GROUP BY TABLESPACE_NAME) F, ");
		sbsql.append("        (SELECT DD.TABLESPACE_NAME, ");
		sbsql.append("                ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB ");
		sbsql.append("           FROM SYS.DBA_DATA_FILES DD 　　GROUP BY DD.TABLESPACE_NAME) D ");
		sbsql.append("  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 　　ORDER BY 1 ");
		sbsql.append("  ");
		List<Map<String, Object>> retList = jdbcTemplate.queryForList(sbsql.toString());
		List<TableSpaceUseDTO> dtoList = new ArrayList<TableSpaceUseDTO>();
		for (Map<String, Object> map : retList) {
			TableSpaceUseDTO tableUse = new TableSpaceUseDTO();
			tableUse.setTablespaceName((String)map.get("TABLESPACE_NAME"));
			tableUse.setTotGrootteMb(String.valueOf(map.get("TOT_GROOTTE_MB")));
			tableUse.setUseGrootteMb(String.valueOf(map.get("USE_GROOTTE_MB")));
			tableUse.setUsePercentage((String)map.get("USE_PERCENTAGE"));
			tableUse.setTotalBytes(String.valueOf(map.get("TOTAL_BYTES")));
			tableUse.setMaxBytes(String.valueOf(map.get("MAX_BYTES")));
			dtoList.add(tableUse);
		}
		return dtoList;
	}

}
